package com.dfd.store.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.DateConverter;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.omg.CORBA.PUBLIC_MEMBER;

import com.dfd.store.dao.OrderDao;
import com.dfd.store.domain.Order;
import com.dfd.store.domain.OrderItem;
import com.dfd.store.domain.Product;
import com.dfd.store.domain.User;
import com.dfd.store.utils.JDBCUtils;
import com.dfd.store.utils.MyBeanUtils;

public class OrderDaoImpl implements OrderDao {
	private QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());

	@Override
	public void saveOrder(Connection conn, Order order) throws Exception {
		String sql = "insert into orders values(?,?,?,?,?,?,?,?)";
		Object[] params = { order.getOid(), order.getOrdertime(), order.getTotal(), order.getState(),
				order.getAddress(), order.getName(), order.getTelephone(), order.getUser().getUid() };
		runner.update(conn, sql, params);
	}

	@Override
	public void saveOrderItem(Connection conn, OrderItem item) throws Exception {
		String sql = "insert into orderitem values(?,?,?,?,?)";
		Object[] params = { item.getItemid(), item.getQuantity(), item.getTotal(), item.getProduct().getPid(),
				item.getOrder().getOid() };
		runner.update(conn, sql, params);
	}

	@Override
	public int getTotalRecords(User user) throws Exception {
		String sql = "select count(*) from orders where uid = ?";
		Long total = (Long) runner.query(sql, new ScalarHandler(), user.getUid());
		return total.intValue();
	}

	@Override
	public List findOrderByUser(User user, int startIndex, int pageSize) throws Exception {
		String sql = "select * from orders where uid =? limit ?,?";
		List<Order> list = runner.query(sql, new BeanListHandler<Order>(Order.class), user.getUid(), startIndex,
				pageSize);
		// 遍历所有订单
		for (Order order : list) {
			// 获取所有的oid
			// select * from orderitem o ,product p where o.pid = p.pid and oid
			// = 'E1D197B3F110466CBCAC24718A4707FE'
			String oid = order.getOid();
			sql = "select * from orderitem o ,product p where o.pid = p.pid and oid = ?";
			List<Map<String, Object>> list2 = runner.query(sql, new MapListHandler(), oid);
			// 遍历list2
			for (Map<String, Object> map : list2) {
				OrderItem orderItem = new OrderItem();
				Product product = new Product();
				DateConverter dt = new DateConverter();
				// 2_设置转换的格式
				dt.setPattern("yyyy-MM-dd");
				// 3_注册转换器
				ConvertUtils.register(dt, java.util.Date.class);
				// 将map中属于orderItem的数据自动填充Orderitem对象上
				BeanUtils.populate(orderItem, map);
				// 将map中属于product的数据自动填充product对象上
				BeanUtils.populate(product, map);
				// 关联
				orderItem.setProduct(product);
				order.getList().add(orderItem);
			}
		}

		return list;
	}

	@Override
	public Order findOrderByOid(String oid) throws Exception {
		String sql = "select * from orders where oid = ?";
		Order order = runner.query(sql, new BeanHandler<Order>(Order.class), oid);
		sql = "select * from orderitem o ,product p where o.pid = p.pid and oid = ?";
		List<Map<String, Object>> list2 = runner.query(sql, new MapListHandler(), oid);
		// 遍历list2
		for (Map<String, Object> map : list2) {
			OrderItem orderItem = new OrderItem();
			Product product = new Product();
			DateConverter dt = new DateConverter();
			// 2_设置转换的格式
			dt.setPattern("yyyy-MM-dd");
			// 3_注册转换器
			ConvertUtils.register(dt, java.util.Date.class);
			// 将map中属于orderItem的数据自动填充Orderitem对象上
			BeanUtils.populate(orderItem, map);
			// 将map中属于product的数据自动填充product对象上
			BeanUtils.populate(product, map);
			// 关联
			orderItem.setProduct(product);
			order.getList().add(orderItem);

		}
		return order;
	}

	@Override
	public void updataOrder(Order order) throws Exception {
		String sql = "update orders set ordertime=?,total=?,state=?,address=?,name=?,telephone=? where oid =?";
		Object[] params = { order.getOrdertime(), order.getTotal(), order.getState(), order.getAddress(),
				order.getName(), order.getTelephone(), order.getOid() };

		runner.update(sql, params);
	}

	@Override
	public int getTotalRecords() throws Exception {
		String sql = "select count(*) from orders";
		Long total = (Long) runner.query(sql, new ScalarHandler());

		return total.intValue();
	}

	@Override
	public List<Order> findAll() throws Exception {
		String sql = "select * from orders order by ordertime desc";
		return runner.query(sql, new BeanListHandler<Order>(Order.class));
	}

	@Override
	public List<Order> findByState(int state) throws Exception {
		String sql = "select * from orders where state = ? order by ordertime desc  ";
		return runner.query(sql, new BeanListHandler<Order>(Order.class), state);

	}

}